﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SSS.Common;
using System.Data;
using MySql.Data.MySqlClient;
using SSS.Core.Model;


namespace SSS.DAL
{
    public class InventoryDB
    {
        public static string test()
        {
            InventoryType t = new InventoryType("978-7-115-27579-1", "JavaScript高级程序设计2", "本", "编程开发", "javascript.png");
            List<Inventory> l = new List<Inventory>();
            l.Add(new Inventory(0, DateTime.Now, "admin", 
            DateTime.MinValue, null, DateTime.MinValue,
            null, 1, "978-7-115-27579-1", "本",
            100, 99, t ));
            l.Add(new Inventory(0, DateTime.Now, "admin", 
            DateTime.MinValue, null, DateTime.MinValue,
            null, 1, "978-7-115-27579-1", "本",
            101, 98, t ));
            Purchase purchase = new Purchase(DateTime.Now, "采购员2", "admin", 1, "仓库1", l);
            return purchase.ToString();
            //AddPurchase( "admin", "111111", "127.0.0.1", purchase );

        }


        /// <summary>
        /// 添加商品类型
        /// </summary>
        /// <param name="ID">用户</param>
        /// <param name="Pwd">用户密码</param>
        /// <param name="IP">用户IP</param>
        /// <param name="InventoryID">库存ID</param>
        /// <param name="InventoryName">库存物品名字</param>
        /// <param name="InventoryUnit">库存商品的单位</param>
        /// <param name="InventoryLable">库存物品的商品类型</param>
        /// <param name="InventoryPicurl">库存物品的图片地址</param>
        /// <returns></returns>
        public static EnumType.ResultState AddInventoryType( string ID, string Pwd, string IP, string InventoryID, string InventoryName, string InventoryUnit, string InventoryLable, string InventoryPicurl )
        {

            #region 存储过程

            //////////////////////////////////////////////////////////////////////////////
            //添加商品类型
            //存储过程为：sp_inventorytype_add
            //参数：
            //      IN `p_user_id` varchar(255),
            //      IN `p_user_pwd` varchar(255),
            //      IN `p_ip` varchar(255),
            //      IN `p_inventory_id` varchar(255),
            //      IN `p_inventory_name` varchar(255),
            //      IN `p_inventory_unit` varchar(255),
            //      IN `p_inventory_label` varchar(255),
            //      IN `p_inventory_picurl` varchar(255),
            //      OUT `p_result` varchar(255)
            //////////////////////////////////////////////////////////////////////////////

            #endregion

            try
            {
                // 指定需要调用的存储过程:修改密码
                DBProvider.MyCommand.CommandText = "sp_inventorytype_add";

                // 设置存储过程参数
                DBProvider.Parameters[ "p_user_id", MySqlDbType.VarChar, 255 ] = ID;
                // 客户端已对密码加密，所以这里不需要再次加密
                DBProvider.Parameters[ "p_user_pwd", MySqlDbType.VarChar, 255 ] = Pwd;
                DBProvider.Parameters[ "p_ip", MySqlDbType.VarChar, 255 ] = IP;
                DBProvider.Parameters[ "p_inventory_id", MySqlDbType.VarChar, 255 ] = InventoryID;
                DBProvider.Parameters[ "p_inventory_name", MySqlDbType.VarChar, 255 ] = InventoryName;
                DBProvider.Parameters[ "p_inventory_unit", MySqlDbType.VarChar, 255 ] = InventoryUnit;
                DBProvider.Parameters[ "p_inventory_label", MySqlDbType.VarChar, 255 ] = InventoryLable;
                DBProvider.Parameters[ "p_inventory_picurl", MySqlDbType.VarChar, 255 ] = InventoryPicurl;

                DBProvider.Parameters[ "p_result", MySqlDbType.VarChar, 255, ParameterDirection.Output ] = 0;

                // 打开连接
                DBProvider.Open();
                // 执行存储过程并返回结果
                DBProvider.ExecuteNonQuery();

                // 判断登录结果
                switch ( (string)DBProvider.Parameters[ "p_result" ] )
                {
                    case "成功":
                        return EnumType.ResultState.成功;
                    default:
                        return EnumType.ResultState.未知错误;
                }
            }
            catch
            {
                return EnumType.ResultState.未知错误;
            }
            finally
            {
                // 关闭数据库连接
                DBProvider.Close();
            }

        }




        /// <summary>
        /// 入库
        /// </summary>
        /// <param name="ID">用户ID</param>
        /// <param name="Pwd">用户密码</param>
        /// <param name="IP">用户IP</param>
        /// <param name="InventoryID">入库商品库存ID</param>
        /// <param name="InventoryCount">入库商品数量</param>
        /// <param name="InventoryName">入库商品名字</param>
        /// <param name="InventoryPrice">入库商品价格</param>
        /// <returns></returns>
        public static int AddInventory( string ID, string Pwd, string IP, string InventoryID, int InventoryCount,  double InventoryPrice, string DepotName )
        {

            #region 存储过程
            //////////////////////////////////////////////////////////////////////////////
            //入库
            //存储过程为：sp_inventory_in
            //参数：
            //      IN `p_user_id` varchar(255),
            //      IN `p_user_pwd` varchar(255),
            //      IN `p_ip` varchar(255),
            //      IN `p_inventory_id` varchar(255),
            //      IN `p_inventory_count` int,
            //      IN `p_purchase_price` double,
            //      IN `p_depot_name` varchar(255),
            //      OUT `p_inventory_no` int
            //////////////////////////////////////////////////////////////////////////////

            #endregion

            try
            {
                // 设置存储过程参数
                DBProvider.Parameters[ "p_user_id", MySqlDbType.VarChar, 255 ] = ID;
                // 客户端已对密码加密，所以这里不需要再次加密
                DBProvider.Parameters[ "p_user_pwd", MySqlDbType.VarChar, 255 ] = Pwd;
                DBProvider.Parameters[ "p_ip", MySqlDbType.VarChar, 255 ] = IP;
                DBProvider.Parameters[ "p_inventory_id", MySqlDbType.VarChar, 255 ] = InventoryID;
                DBProvider.Parameters[ "p_inventory_count", MySqlDbType.Double ] = InventoryCount;
                DBProvider.Parameters[ "p_purchase_price", MySqlDbType.Int32 ] = InventoryPrice; ;
                DBProvider.Parameters[ "p_depot_name", MySqlDbType.VarChar, 255 ] = DepotName;


                DBProvider.Parameters[ "p_inventory_no", MySqlDbType.Int32, 11, ParameterDirection.Output ] = 0;

                // 打开连接
                DBProvider.Open();
                // 执行存储过程并返回结果
                DBProvider.ExecuteNonQuery("sp_inventory_in");

                return (int)DBProvider.Parameters[ "p_inventory_no"];

            }
            catch
            {
                return -1;
            }
            finally
            {
                // 关闭数据库连接
                DBProvider.Close();
            }


        }




        /// <summary>
        /// 提交入库单
        /// </summary>
        /// <param name="ID">用户ID</param>
        /// <param name="Pwd">用户密码</param>
        /// <param name="IP">用户IP</param>
        /// <param name="purchase">入库单对象</param>
        /// <returns></returns>
        public static int AddPurchase( string ID, string Pwd, string IP, Purchase purchase )
        {

            #region 存储过程
            //////////////////////////////////////////////////////////////////////////////
            //提交入库单
            //存储过程为：sp_purchase_add
            //参数：
            //      IN `p_user_id` varchar(255),
            //      IN `p_user_pwd` varchar(255),
            //      IN `p_ip` varchar(255),
            //      IN `p_purchase_time` datetime ,
            //      IN `p_purchase_user_id` varchar(255),
            //      IN `p_storage_user_id` varchar(255),
            //      IN `p_inventory_nos` text,
            //      OUT `p_purchase_no` int
            //////////////////////////////////////////////////////////////////////////////
            #endregion

            try
            {
                string InventoryNos = "";

                for (int i = 0; i < purchase.InventoryList.Count; i++)
                {
                    AddInventoryType(ID, Pwd, IP, purchase.InventoryList[i].InventoryId, purchase.InventoryList[i].Type.InventoryName,
                        purchase.InventoryList[i].Type.InventoryUnit, purchase.InventoryList[i].Type.InventoryLabel, purchase.InventoryList[i].Type.InventoryPicurl);
                    if(i < purchase.InventoryList.Count - 1)
                    {
                        InventoryNos += AddInventory( ID, Pwd, IP, purchase.InventoryList[ i ].InventoryId, purchase.InventoryList[ i ].InventoryCount, purchase.InventoryList[ i ].PurchasePrice, purchase.DepotName ).ToString();
                        InventoryNos += ",";                   
                    }
                    else
                    {
                        InventoryNos += AddInventory( ID, Pwd, IP, purchase.InventoryList[ i ].InventoryId, purchase.InventoryList[ i ].InventoryCount, purchase.InventoryList[ i ].PurchasePrice, purchase.DepotName ).ToString();
                    }

                }

                // 设置存储过程参数
                DBProvider.Parameters["p_user_id", MySqlDbType.VarChar, 255] = ID;
                // 客户端已对密码加密，所以这里不需要再次加密
                DBProvider.Parameters["p_user_pwd", MySqlDbType.VarChar, 255] = Pwd;
                DBProvider.Parameters["p_ip", MySqlDbType.VarChar, 255] = IP;
                DBProvider.Parameters["p_purchase_time", MySqlDbType.DateTime] = purchase.PurchaseTime;
                DBProvider.Parameters["p_purchase_user_id", MySqlDbType.VarChar, 255] = purchase.PurchaseUserId;
                DBProvider.Parameters["p_storage_user_id", MySqlDbType.VarChar, 255] = purchase.StorageUserId;
                DBProvider.Parameters[ "p_inventory_nos", MySqlDbType.VarChar, 255 ] = InventoryNos;
                DBProvider.Parameters[ "p_purchase_no", MySqlDbType.Int32, 11, ParameterDirection.Output ] = 0;

                // 打开连接
                DBProvider.Open();
                // 执行存储过程并返回结果
                DBProvider.ExecuteNonQuery("sp_purchase_add");

                return (int)DBProvider.Parameters[ "p_inventory_no" ];

            }
            catch
            {
                return -1;
            }
            finally
            {
                // 关闭数据库连接
                DBProvider.Close();
            }

        }
    }
}
